﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Configuration;
using KPIS.DBM;
using KPIS.GERP.SECURITY.MODAL;
using KPIS.GERP.SECURITY.MODEL;
using System.Linq;

namespace KPIS.GERP.SECURITY.DAL
{
    public class UserRoleDAL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable LoadUserRole(Nullable<int> Sequence, Nullable<int> UserSequence)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@UserSequence", UserSequence);
                dbManager.AddParameters(2, "@RecordStatus", SystemInfo._ActiveRecordStatus);

                strSQL = "SELECT a.*"
                    + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS USER_NAME"
                    + ", r.ROLE_NAME"
                    //+ ", u.user_id, u.user_name, r.role_name, r.remarks"
                    + " FROM SYS_SC_USER_ROLES a"
                    + " LEFT JOIN SYS_SC_ROLES r ON a.ROLE_SEQ = r.ROLE_SEQ"
                    + " LEFT JOIN SYS_SC_USERS u ON a.USER_SEQ = u.USER_SEQ"
                    + " LEFT JOIN HRM_PS_EMPLOYEES e ON u.EMP_SEQ = e.EMP_SEQ"
                    + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    + " WHERE a.RECORD_STATUS = @RecordStatus"
                    + (Sequence.HasValue ? " AND a.USER_ROLE_SEQ = @Sequence" : "")
                    + (UserSequence.HasValue ? " AND u.USER_SEQ = @UserSequence" : "");

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }
        public DataTable LoadUserRoleUnion(Nullable<int> Sequence, Nullable<int> UserSequence, string SystemID)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@UserSequence", UserSequence);
                dbManager.AddParameters(2, "@SystemID", SystemID);
                dbManager.AddParameters(3, "@RecordStatus", SystemInfo._ActiveRecordStatus);

                strSQL = "SELECT t.* "
                    + " FROM("
                        + " SELECT a.USER_ROLE_SEQ, a.USER_SEQ, a.ROLE_SEQ"
                        + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS USER_NAME"
                        + ", r.ROLE_NAME, r.SYSTEM AS SYSTEM_ID"
                        + ", 1 AS HAS_ROLE"
                        + " FROM SYS_SC_USER_ROLES a"
                        + " LEFT JOIN SYS_SC_ROLES r ON a.ROLE_SEQ = r.ROLE_SEQ"
                        + " LEFT JOIN SYS_SC_USERS u ON a.USER_SEQ = u.USER_SEQ"
                        + " LEFT JOIN HRM_PS_EMPLOYEES e ON u.EMP_SEQ = e.EMP_SEQ"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " WHERE a.RECORD_STATUS = @RecordStatus"
                        + (Sequence.HasValue ? " AND a.USER_ROLE_SEQ = @Sequence" : "")
                        + (UserSequence.HasValue ? " AND u.USER_SEQ = @UserSequence" : "")
                        + (SystemID.Length > 0 ? " AND r.SYSTEM = @SystemID" : "")
                        + " UNION ALL"
                        + " SELECT NULL AS USER_ROLE_SEQ"
                            + ", NULL AS USER_SEQ"
                            + ", rr.ROLE_SEQ"
                            + ", NULL AS USER_NAME, rr.ROLE_NAME, rr.SYSTEM AS SYSTEM_ID"
                            + ", 0 AS HAS_ROLE"
                        + " FROM SYS_SC_ROLES rr"
                        + " WHERE rr.RECORD_STATUS = @RecordStatus"
                        + (SystemID.Length > 0 ? " AND rr.SYSTEM = @SystemID" : "")
                        + " AND rr.ROLE_SEQ NOT IN("
                                + " select role_seq from sys_sc_user_roles"
                                + " where system = @SystemID "
                                + " and record_status = @RecordStatus"
                                + " and user_seq = @UserSequence )"
                    + ")t";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        } 
        public void Insert(int UserSequence, int RoleSequence, int CreatedBy)
        {
            try
            {
                string strSQL;

                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@UserSequence", UserSequence);
                dbManager.AddParameters(1, "@RoleSequence", RoleSequence);
                dbManager.AddParameters(2, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(3, "@CreatedBy", CreatedBy);
                dbManager.AddParameters(4, "@CreatedWhen", DateTime.Now);

                strSQL = "INSERT INTO SYS_SC_USER_ROLES"
                    + "(USER_SEQ, ROLE_SEQ, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@UserSequence, @RoleSequence, @RecordStatus, @CreatedBy, @CreatedWhen)";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(int Sequence, int UserSequence, int RoleSequence, int UpdatedBy)
        {
            try
            {
                string strSQL;

                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@UserSequence", UserSequence);
                dbManager.AddParameters(2, "@RoleSequence", RoleSequence);
                dbManager.AddParameters(3, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(4, "@UpdatedWhen", DateTime.Now);

                strSQL = "UPDATE SYS_SC_USER_ROLES"
                    + " SET USER_SEQ = @UserSequence"
                        + ", ROLE_SEQ = @RoleSequence"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE USER_ROLE_SEQ = @Sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            try
            {
                string strSQL;

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Now);

                strSQL = "UPDATE SYS_SC_USER_ROLES"
                    + " SET RECORD_STATUS = @DeleteRecord"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE USER_ROLE_SEQ = @Sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public List<UserWithRoles> GetUsersAsObjectUserWithRoles()
        {
            List<UserWithRoles> result = new List<UserWithRoles>();
            try
            {
                string strSQL;

                dbManager.Open();
                strSQL = @"
SELECT u.user_seq, u.user_code AS 'UserName', 
	u.user_password AS 'Password', 
	CONCAT(COALESCE(np.prefix_name,''), ' ', COALESCE(e.first_name,''), ' ', COALESCE(e.last_name,'')) AS 'FullName',
	CONCAT(mp.pos_ref, ' - ', COALESCE(mp.pos_name,'')) AS 'Position',
    js.job_site_name AS 'JobSite'
FROM sys_sc_users u 
	INNER JOIN dbo.hrm_ps_employees AS e ON u.emp_seq = e.emp_seq
	INNER JOIN dbo.hrm_ps_employee_statuses AS es ON e.emp_status_seq = es.emp_status_seq AND es.Active = '1'
	LEFT JOIN dbo.mas_name_prefixes AS np ON e.name_prefix_seq = np.name_prefix_seq
	LEFT JOIN dbo.hrm_mp_positions AS mp ON e.pos_seq = mp.pos_seq 
	LEFT JOIN dbo.hrm_mp_job_sites as js ON e.working_job_site_seq = js.job_site_seq
	LEFT JOIN dbo.sys_sc_user_roles AS ur ON u.user_seq = ur.user_seq
	LEFT JOIN dbo.sys_sc_roles AS r ON ur.role_seq = r.role_seq

WHERE e.record_status != 'D'
GROUP BY u.user_seq, u.user_code, u.user_password, np.prefix_name, e.first_name, e.last_name, mp.pos_ref, mp.pos_name, js.job_site_name
ORDER BY mp.pos_ref";

                foreach (DataRow dr in dbManager.ExecuteDataTable(CommandType.Text, strSQL).Rows)
                {
                    result.Add(new UserWithRoles
                        {
                            UserSeq = Convert.ToInt32(dr["user_seq"]),
                            UserName = Convert.ToString(dr["userName"]),
                            Password = Convert.ToString(dr["Password"]),
                            FullName = Convert.ToString(dr["FullName"]),
                            Position = Convert.ToString(dr["Position"]),
                            JobSite = Convert.ToString(dr["JobSite"]),
                        });
                }
                
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return result;
        }

        public List<RolesInUser> GetUsersRoles()
        {
            List<RolesInUser> result = new List<RolesInUser>();
            try
            {
                string strSQL;

                dbManager.Open();
                strSQL = @"
SELECT ur.user_seq, ml.system_id system, r.remarks
FROM  sys_sc_users u 
	INNER JOIN dbo.sys_sc_user_roles AS ur ON u.user_seq = ur.user_seq AND ur.record_status != 'D'
	INNER JOIN dbo.sys_sc_roles AS r ON ur.role_seq = r.role_seq AND r.record_status != 'D'
    INNER JOIN SYS_SC_PERMISSIONS p ON r.ROLE_SEQ = p.ROLE_SEQ AND p.record_status != 'D'
    INNER JOIN SYS_MENU_ITEMS_LIST ml ON p.MENU_ITEM_SEQ = ml.MENU_ITEM_SEQ AND ml.record_status != 'D'
WHERE u.record_status != 'D'
GROUP BY ur.user_seq, ml.system_id, r.remarks
ORDER BY ur.user_seq, ml.system_id, r.remarks;";

                foreach (DataRow dr in dbManager.ExecuteDataTable(CommandType.Text, strSQL).Rows)
                {
                    result.Add(new RolesInUser
                        {
                            UserSeq = Convert.ToInt32(dr["user_seq"]),
                            System = Convert.ToString(dr["system"]),
                            RoleDescription = Convert.ToString(dr["remarks"])
                        });
                }
                
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return result;
        }
    }
}
